<?php
/**
 * 简易Excel操作,依赖PHPExcel
 * @author Elwin
 * 
 * example:
 * 
 * $execlDir = 'E:\\work\\ework\\protected\\aa.xls';		
 * $excel = new CDbExcel ();
 * $excel->loadExcelFile ( $execlDir, array (
 *    'dataRow' => 3,'titleRow' => 2 
 * ) );
 * 
 * $excel->parseExcel ();
 * 
 * Array ( [A] => 咨询编号 [B] => 咨询来源 ......)
 * 
 * $excel->setExcelFieldName ( array (
 *	  'A' => 't.id','B' => 't.consultsource',
 * ));
 * 
 * $tablesData = $excel->mergeTablesData ();
 * 
 */
class CDbExcel
{
	/**
	 * 要解析的Excel文件名称
	 * 
	 * @var string $filename
	 */
	public $filename;
	/**
	 * Excel解析结果数据
	 * 
	 * @var $dataRow
	 */
	public $dataRow;
	/**
	 * Excel解析结果标题标题
	 * 
	 * @var $titleRow
	 */
	public $titleRow;
	/**
	 * Excel列标题对应的字段名称
	 * 
	 * @var unknown_type
	 */
	public $excelTitleColumn;
	/**
	 * Excel解析结果数据
	 * 
	 * @var $dataRows
	 */
	public $dataRows;
	/**
	 * Excel列对应的字段名称
	 */
	public $excelFieldName;
	
	/**
	 * 
	 */
	public function init()
	{
		parent::init ();
	}
	
	/**
	 * $settings = array(
	 * 'dataRow'=>'Excel数据起始行',
	 * 'titleRow'=>'标题所在行'
	 * );
	 *
	 * @param string $filename
	 * @param array $settings
	 * @throws CHttpException
	 */
	public function loadExcelFile($filename, $settings = array())
	{
		if (! file_exists ( $filename ))
			throw new CHttpException ( 403, '没有找到excel文件' );
		$this->filename = $filename;
		
		if (is_array ( $settings ) && ! empty ( $settings ))
		{
			foreach ( $settings as $name => $value )
				$this->$name = $value;
		}
	}
	
	/**
	 * 解析Excel文件成数组
	 */
	public function parseExcel()
	{
		spl_autoload_unregister ( array (
				'YiiBase','autoload' 
		) );
		// 载入Excel类文件,加载方法
		include_once (Yii::app ()->basePath . '/vendors/excel/PHPExcel.php');
		include_once (Yii::app ()->basePath . '/vendors/excel/PHPExcel/IOFactory.php');
		$objExcel = new PHPExcel ();
		
		// 读入上传文件
		$objPHPExcel = PHPExcel_IOFactory::load ( $this->filename );
		
		$sheet = $objPHPExcel->getActiveSheet ();
		// 取得总行数
		$highestRow = $sheet->getHighestRow ();
		
		$highestColumn = $sheet->getHighestColumn (); // 取得总列数最大字母 如A..AA..BA
		
		if (strlen ( $highestColumn ) == 2)
			$highestColumn = 'Z';
		
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString ( $highestColumn );
		$headtitle = array ();
		
		// 设置数据起始行数
		for($row = 1; $row <= $highestRow; $row ++)
		{
			$strs = array ();
			// 注意highestColumnIndex的列数索引从0开始
			$count = 0;
			for($col = 'A'; $col <= $highestColumn; $col ++)
			{
				// 避免$highestColumn=Z时,过多循环
				if ($count > $highestColumnIndex)
					continue;
				$count ++;
				$sheetColName = $col . $row;
				$value = $sheet->getCell ( $sheetColName )->getValue ();
				$sheetColName = preg_replace ( '/(\\d+)/', '', $col . $row );
				// 标题行
				if ($this->titleRow == $row)
				{
					// array('A'=>'标题名称',......);
					$this->excelTitleColumn [$sheetColName] = $value;
				} else if ($row >= $this->dataRow)
				{
					$this->dataRows [$row - $this->dataRow] [$sheetColName] = $value;
				}
			}
		}
		spl_autoload_register ( array (
				'YiiBase','autoload' 
		) );
	}
	
	/**
	 * 设置Excel标题对应的数据表字段名称
	 * 如:
	 * array(
	 *   'A1'=>t.id,
	 *   'A2'=>t.consultsource,
	 *   'A3'=>consult.channel,
	 *   ......
	 * )
	 */
	public function setExcelFieldName($columns)
	{
		$this->excelFieldName = $columns;
	}
	
	/**
	 * return array(
	 *   'A1'=>t.id,
	 *   'A2'=>t.consultsource,
	 *   'A3'=>consult.channel,
	 *   ......
	 * )
	 * @return array
	 */
	public function getExcelFieldName()
	{
		return $this->excelFieldName;
	}
	
	/**
	 * Array
	 * (
	 * [t.id] => 咨询编号
	 * [t.consultsource] => 咨询来源
	 * [consult.channel] => 咨询通道
	 * ......
	 * )
	 *
	 * @return array
	 */
	public function getExcelTitleColumn()
	{
		$count = count ( $this->excelFieldName );
		$size = count ( $this->excelTitleColumn );
		if ($count == $size)
			$column = array_combine ( $this->excelFieldName, $this->excelTitleColumn );
		else
			throw new CHttpException ( 500, '两个数组元素不相等,处理错误!' );
		return $column;
	}
	
	/**
	 * 
	 * Array
	 * (
	 * [t.id] => 1
	 * [t.consultsource] => EC
	 * [consult.channel] => 商务通
	 * ......
	 * )
	 * 
	 * @return array
	 */
	public function getDataRows()
	{
		return $this->dataRows;
	}
	
	/**
	 * Excel中包括多个表的字段时,使用此方法进行拆分($this->dataRows)数组中的数据
	 *
	 * @return $tablesData
	 */
	public function mergeTablesData()
	{
		$title = $this->getExcelTitleColumn ();
		$data = $this->dataRows;
		$tablesData = array ();
		foreach ( $data as $num => $row )
		{
			foreach ( $this->getExcelFieldName () as $k => $column )
			{
				if (strpos ( $column, '.' ))
					$prefix = strstr ( $column, '.', true );
				else
					$prefix = 't';
				$column = preg_replace ( '/(\w+?\.)/', '', $column );
				$tablesData [$prefix] [$num] [$column] = $row [$k];
			}
		}
		return $tablesData;
	}
	
	/**
	 * * PHP wakeup magic method.
	 * * This method ensures that the model meta data is ready when
	 * deserializing an AR object.
	 */
	public function __wakeup()
	{
		$this->objExcel = new self ();
	}
	/**
	 * * PHP sleep magic method.
	 * * This method ensures that the model meta data reference is set to null.
	 */
	public function __sleep()
	{
		$this->objExcel = null;
		return array_keys ( ( array ) $this );
	}
}
?>